﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using SistemadeEstágios.Model;

namespace SistemadeEstágios.DAL
{
    /// <summary>
    /// Factory de Acesso a Dados do Estagiario
    /// </summary>
    public class EstagiarosBD : BaseBD
    {

        #region Busca

        public Estagiaros Buscar(int codigo)
        {
            Estagiaros obj = null;

            string query = @"SELECT matricula, nome, endereco, cidade, cep,
                        curso, semestre, telefone, email, rg,
                        cpf, sexo, dt_insercao FROM Estagiarios Where matricula = @codigo;";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", codigo);

                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    obj = new Estagiaros();

                    obj.Matricula = Int32.Parse(oDbManager.DBReader["matricula"].ToString());
                    obj.Nome = oDbManager.DBReader["nome"].ToString();
                    obj.Sexo = Char.Parse(oDbManager.DBReader["sexo"].ToString());
                    obj.RG = oDbManager.DBReader["rg"].ToString();
                    obj.Cpf = oDbManager.DBReader["cpf"].ToString();
                    obj.Email = oDbManager.DBReader["email"].ToString();
                    obj.Telefone = oDbManager.DBReader["telefone"].ToString();
                    obj.Curso = new Cursos();
                    obj.Curso.Codigo = Int32.Parse(oDbManager.DBReader["curso"].ToString());
                    obj.Semestre = Int32.Parse(oDbManager.DBReader["semestre"].ToString());
                    obj.CEP = oDbManager.DBReader["cep"].ToString();
                    obj.Cidade = oDbManager.DBReader["cidade"].ToString();
                    obj.Endereco = oDbManager.DBReader["endereco"].ToString();

                }

            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return obj;
        }

        public List<Estagiaros> Buscar()
        {
            List<Estagiaros> lista = null;
            Estagiaros obj = null;

            string query = @"SELECT matricula, nome, endereco, cidade, cep,
                        curso, semestre, telefone, email, rg,
                        cpf, sexo, dt_insercao FROM Estagiarios;";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.ExecuteReader(query);

                while (oDbManager.DBReader.Read())
                {
                    if (lista == null) lista = new List<Estagiaros>();

                    obj = new Estagiaros();

                    obj.Matricula = Int32.Parse(oDbManager.DBReader["matricula"].ToString());
                    obj.Nome = oDbManager.DBReader["nome"].ToString();
                    obj.Sexo = Char.Parse(oDbManager.DBReader["sexo"].ToString());
                    obj.RG = oDbManager.DBReader["rg"].ToString();
                    obj.Cpf = oDbManager.DBReader["cpf"].ToString();
                    obj.Email = oDbManager.DBReader["email"].ToString();
                    obj.Telefone = oDbManager.DBReader["telefone"].ToString();
                    obj.Curso = new Cursos();
                    obj.Curso.Codigo = Int32.Parse(oDbManager.DBReader["curso"].ToString());
                    obj.Semestre = Int32.Parse(oDbManager.DBReader["semestre"].ToString());
                    obj.CEP = oDbManager.DBReader["cep"].ToString();
                    obj.Cidade = oDbManager.DBReader["cidade"].ToString();
                    obj.Endereco = oDbManager.DBReader["endereco"].ToString();

                    lista.Add(obj);
                }

            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return lista;
        }

        #endregion

        #region Inserir

        public bool Insere(Estagiaros obj)
        {
            int r = -1;
            string query = @"INSERT INTO Estagiarios VALUES
                    (@matricula, @nome, @endereco, @cidade,
                    @cep, @curso, @semestre, @telefone, @email,
                    @rg, @cpf, @sexo, @dt_insercao);";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@matricula", obj.Matricula);
                oDbManager.AddParameter("@nome", obj.Nome);
                oDbManager.AddParameter("@endereco", obj.Endereco);
                oDbManager.AddParameter("@cidade", obj.Cidade);
                oDbManager.AddParameter("@cep", obj.CEP);
                oDbManager.AddParameter("@curso", obj.Curso.Codigo);
                oDbManager.AddParameter("@semestre", obj.Semestre);
                oDbManager.AddParameter("@telefone", obj.Telefone);
                oDbManager.AddParameter("@email", obj.Email);
                oDbManager.AddParameter("@rg", obj.RG);
                oDbManager.AddParameter("@cpf", obj.Cpf);
                oDbManager.AddParameter("@sexo", obj.Sexo);
                oDbManager.AddParameter("@dt_insercao", DateTime.Now);

                r = oDbManager.ExecuteNonQuery(query, CommandType.Text);
            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        #endregion

        #region Historico

        public bool CriaHistorico(int codigo)
        {
            int r = -1;
            string query = @"Insert Into Hst_Estagiarios SELECT (Select Max(codigo) + 1 from Hst_Estagiarios), matricula, 
	                        nome, endereco, cidade, cep, curso, semestre, telefone, email, rg, cpf, sexo,
                            now() FROM Estagiarios Where matricula = @codigo;";

            try
            {
                oDbManager.OpenConnection();
                oDbManager.AddParameter("@codigo", codigo);

                r = oDbManager.ExecuteNonQuery(query, CommandType.Text);
            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        #endregion

        #region Alterar

        public bool Alterar(Estagiaros obj)
        {
            int r = -1;
            string query = string.Empty;

            try
            {
                query = @"UPDATE Estagiarios SET nome = @nome, endereco = @endereco, 
                        cidade = @cidade, cep = @cep, curso = @curso, semestre = @semestre,
                        telefone = @telefone, email = @email, rg = @rg, cpf = @cpf,
                        sexo = @sexo, dt_insercao = @dt_insercao WHERE matricula = @matricula;";

                oDbManager.OpenConnection();
                oDbManager.AddParameter("@nome", obj.Nome);
                oDbManager.AddParameter("@endereco", obj.Endereco);
                oDbManager.AddParameter("@cidade", obj.Cidade);
                oDbManager.AddParameter("@cep", obj.CEP);
                oDbManager.AddParameter("@curso", obj.Curso.Codigo);
                oDbManager.AddParameter("@semestre", obj.Semestre);
                oDbManager.AddParameter("@telefone", obj.Telefone);
                oDbManager.AddParameter("@email", obj.Email);
                oDbManager.AddParameter("@rg", obj.RG);
                oDbManager.AddParameter("@cpf", obj.Cpf);
                oDbManager.AddParameter("@sexo", obj.Sexo);
                oDbManager.AddParameter("@dt_insercao", obj.DtInsercao);
                oDbManager.AddParameter("@matricula", obj.Matricula);

                r = oDbManager.ExecuteNonQuery(query, CommandType.Text);

            }
            finally
            {
                oDbManager.CloseConnection();
            }

            return (r > 0);
        }

        #endregion

        public int Ultimo()
        {
            int retorno = 1;
            string query = string.Empty;

            try
            {
                query = @"Select Max(matricula) + 1 From Estagiarios";

                oDbManager.OpenConnection();
                oDbManager.ExecuteReader(query);

                if (oDbManager.DBReader.Read())
                {
                    if (oDbManager.DBReader[0] != DBNull.Value)
                        retorno = Int32.Parse(oDbManager.DBReader[0].ToString());
                }
            }
            finally
            {
                oDbManager.CloseConnection();
            }
            return retorno;
        }
    }
}
